package org.lq.finance.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.finance.dao.StaffsalaryDao;
import org.lq.finance.entity.StaffSalary;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 骆杨
 * @create 2020-10-13 下午 6:32
 */
@Log4j
public class StaffsalaryDaoImpl implements StaffsalaryDao {
    /**
     * 添加
     * @param staff_salary
     * @return
     */
    @Override
    public int save(StaffSalary staff_salary) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->add");
        try {
            num = qr.update("insert into staff_salary(staff_id, " +
                            "sta_staff_id, " +
                            "total_salary, " +
                            "deduct_salary, " +
                            "real_salary, " +
                            "is_uesd, " +
                            "staff_salary_time, " +
                            "staff_remark) " +
                            "values (?,?,?,?,?,?,?,?)",
                    staff_salary.getStaffId(),
                    staff_salary.getStaStaffId(),
                    staff_salary.getTotalSalary(),
                    staff_salary.getDeductSalary(),
                    staff_salary.getRealSalary(),
                    staff_salary.getIsUesd(),
                    staff_salary.getStaffSalaryTime(),
                    staff_salary.getStaffRemark());
        } catch (SQLException throwables) {
            log.error("添加出现异常",throwables);
        }
        log.info("-->执行结果："+num);
        return num;
    }

    /**
     * 修改
     * @param staff_salary
     * @return
     */
    @Override
    public int update(StaffSalary staff_salary) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->update");
        try {
            num = qr.update("update staff_salary set staff_id=?, " +
                            "sta_staff_id = ?, " +
                            "total_salary = ?, " +
                            "deduct_salary = ?, " +
                            "real_salary = ?, " +
                            "is_uesd = ?, " +
                            "staff_salary_time = ?, " +
                            "staff_remark = ?" +
                            "where staff_salary_id = ?",
                    staff_salary.getStaffId(),
                    staff_salary.getStaStaffId(),
                    staff_salary.getTotalSalary(),
                    staff_salary.getDeductSalary(),
                    staff_salary.getRealSalary(),
                    staff_salary.getIsUesd(),
                    staff_salary.getStaffSalaryTime(),
                    staff_salary.getStaffRemark(),
                    staff_salary.getStaffSalaryId());
        } catch (SQLException throwables) {
            log.error("修改出现异常",throwables);
        }
        log.info("-->执行结果："+num);
        return num;
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->delete");
        try {
            num = qr.update("delete from staff_salary where staff_salary_id = ?",id);
        } catch (SQLException throwables) {
            log.error("删除出现异常",throwables);
        }
        log.info("-->执行结果："+num);
        return num;
    }

    /**
     * 根据ID查询
     * @param id
     * @return
     */
    @Override
    public StaffSalary getById(int id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        StaffSalary staff_salary = null;
        log.info("开始执行数据访问层.[StaffsalaryDao]->getByid");
        try {
            staff_salary = qr.query("select * from staff_salary where staff_salary_id = ?",new BeanHandler<>(StaffSalary.class),id);
        } catch (SQLException throwables) {
            log.error("id查询出现异常",throwables);
        }
        log.info("-->执行结果："+staff_salary);
        return staff_salary;
    }

    /**
     * 总行数
     * @return
     */
    @Override
    public int getCount() {
        int count = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->getcount");
        try {
            Long num= qr.query("select count(1) from staff_salary",new ScalarHandler<Long>());
            count = num.intValue();
        } catch (SQLException throwables) {
            log.error("总行数出现异常:",throwables);
        }
        log.info("-->执行结果："+count);
        return count;
    }

    /**
     * 分页查询
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<StaffSalary> pageList(int startIndex, int pageSize) {
        List<StaffSalary> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->pageList");
        try {
            list = qr.query("select * from staff_salary limit ?,?",
                    new BeanListHandler<StaffSalary>(StaffSalary.class),
                    startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("分页查询出现异常：",throwables);
        }
        log.info("-->执行结果："+list);
        return list;
    }

    /**
     * 根据条件查询总行数
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        int count =0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("开始执行数据访问层.[StaffsalaryDao]->getCount(String... values)");
        try {
            Long num = qr.query("select count(1) from staff_salary where total_salary like ?",new ScalarHandler<Long>(),"%"+values[0]+"%");
            count = num.intValue();
        } catch (SQLException throwables) {
            log.error("根据条件查询总行数出现异常:",throwables);
        }
        log.info("-->执行结果："+count);
        return count;
    }

    /**
     * 根据条件分页查询
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<StaffSalary> pageByValues(int startIndex, int pageSize, String... value) {
          List<StaffSalary> list = new ArrayList<>();
          QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
          log.info("-->开始执行数据访问层.[StaffsalaryDao]->pageByValues");
        try {
            list = qr.query("select * from staff_salary where total_salary like ? limit ?,?",
                    new BeanListHandler<StaffSalary>(StaffSalary.class),"%"+value[0]+"%",startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("根据条件分页查询出现异常：",throwables);
        }
        log.info("-->执行结果："+list);
        return list;
    }


}
